
--Se cambia a la base de datos Master
USE Master;
GO

--Determina si esta la base de datos PIS 
if exists(Select * FROM SysDataBases WHERE name='PIS')
BEGIN
	--BORRA LA BASE DE DATOS PIS (OJO CON ESTO)
	DROP DATABASE PIS
END

--Crea la base de datos Proyecto en el lugar especificado
--Notar que la base de datos puede tener nombre diferente al archivo
CREATE DATABASE PIS
ON(
	NAME=PIS,
	FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PIS.mdf'
)
GO
---------------------------------------------------------------
USE PIS;
GO

--Se crean tablas con Primary key y Foreign Key
--El comando PRIMARY KEY indica que ese campo es clave primaria

CREATE TABLE Clientes(
	CliCod int NOT NULL PRIMARY KEY IDENTITY(1,1),
	CliNom varchar(40) NOT NULL
) 
GO


CREATE TABLE Sistemas(
	SisCod int NOT NULL PRIMARY KEY IDENTITY(1,1),
	SisNom varchar(40)  NOT NULL,
	SisSit varchar(256) NOT NULL,
	SisUrlWS varchar(256) NOT NULL,	
	CliCod int NOT NULL FOREIGN KEY REFERENCES Clientes(CliCod)
) 
GO

CREATE TABLE WorkPackagesEstados(
	WkpEstCod int NOT NULL PRIMARY KEY IDENTITY(1,1),
	WkpEstNom varchar(40) NOT NULL,
	WkpEstIni varchar(1) NULL,
	WkpEstFin varchar(1) NULL
) 
GO

CREATE TABLE WorkPackages(
	WkpCod int NOT NULL PRIMARY KEY IDENTITY(1,1),
	WkpNom varchar(40) NOT NULL,
	WkpFchIni datetime NULL,
	WkpFchEnt datetime NULL,
	WkpFchEntEst datetime NULL,
	WkpFchFin datetime NULL,
	WkpEstCod int NOT NULL FOREIGN KEY REFERENCES WorkPackagesEstados(WkpEstCod),
	SisCod int NOT NULL FOREIGN KEY REFERENCES Sistemas(SisCod)
) 
GO


CREATE TABLE Usuarios(
	UsrCod int NOT NULL PRIMARY KEY IDENTITY (1,1),
	UsrNom varchar(40) NOT NULL,
	FechaSincronizacion datetime NOT NULL CONSTRAINT DF_Usuarios_FechaSincronizacion  DEFAULT (getdate())
) 
GO 


 

INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('BackLog','S', 'N');
INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('Planning','N', 'N');
INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('Implementing','N', 'N');
INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('Testing','N', 'N');
INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('Validation','N', 'N');
INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('Demo','N', 'N');
INSERT INTO WorkPackagesEstados (WkpEstNom, WkpEstIni, WkpEstFin) VALUES ('Cerrado','N', 'S');

--Agregamos clientes
INSERT INTO Clientes (CliNom) VALUES ('BSCH');

--Agregamos Sistemas
INSERT INTO Sistemas (SisNom, CliCod, SisSit, SisUrlWS) VALUES ('IC-Banking', 1 ,'http://localhost/pis2008/mgi', 'http://localhost:9009/SharepointProxy/');
INSERT INTO Sistemas (SisNom, CliCod, SisSit, SisUrlWS) VALUES ('Sistema Test', 1 ,'http://localhost/pis2008/test', 'http://localhost:9009/SharepointProxy/');

--Agregamos Personas
INSERT INTO Usuarios (UsrNom) VALUES ('PIS20080\administrator');

--Agregamos un work package
INSERT INTO WorkPackages (WkpNom, WkpFchIni, WkpFchEnt, WkpFchEntEst, WkpFchFin, WkpEstCod, SisCod) VALUES ('wp1','2008-10-26T21:08:44', null, '2008-10-31T21:08:44', null, 1, 1);
GO


-------------------------------- CLIENTES --------------------------------------
--Agrega un cliente en Clientes y retorna 1 si lo puede agregar, 0 si ya existe uno con el mismo nombre y -1 si NO lo puede agregar
CREATE	PROCEDURE AgregarCliente 	
			@CliNom varchar(40)
		AS
BEGIN 

	--No se puede repetir el nombre de cliente
	IF (EXISTS (SELECT * FROM Clientes WHERE CliNom=@CliNom))
	BEGIN
		RETURN 0;
	END	
	ELSE
	BEGIN
		DECLARE @Error int
		DECLARE @Id int

		BEGIN TRAN
		INSERT Clientes( CliNom ) VALUES ( @CliNom );
		SET @Error=@@ERROR;
		SET @Id= @@Identity

		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN @Id;
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1;
		END	
	END
END
GO



--Elimina un Cliente retorna 1 si lo puede eliminar 0 si no esta y -1 si NO lo puede eliminar por alguna razon, 
--y -2 si el cliente tiene sistemas y en este caso no lo elimina
CREATE PROCEDURE EliminarCliente 
			@CliCod int 
		AS
BEGIN 
	
	IF (NOT EXISTS (SELECT * FROM Clientes WHERE CliCod = @CliCod ))
		RETURN 0
	ELSE IF (EXISTS (SELECT * FROM Sistemas WHERE CliCod = @CliCod))
		RETURN -2
	ELSE
	BEGIN 

		DECLARE @Error int
		BEGIN TRAN
		DELETE Clientes
		WHERE  CliCod = @CliCod
		SET @Error=@@ERROR

		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN 1
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1
		END	
	END
END
GO


--Modifica el cliente de codigo @CliCod y retorna 1 si lo pudo hacer, 0 si no existe el cliente, -1 si NO lo pudo hacer
--y -2 si ya existe un cliente con ese nombre.
CREATE PROCEDURE ModificarCliente
			@CliCod int,
			@CliNom varchar(40)
		AS
BEGIN 
	IF (NOT EXISTS (SELECT * FROM Clientes WHERE CliCod = @CliCod ))
		RETURN 0
	ELSE IF(EXISTS (SELECT * FROM Clientes WHERE CliCod != @CliCod AND CliNom = @CliNom))
		RETURN -2
	ELSE
	BEGIN
		UPDATE Clientes
		SET CliNom = @CliNom
		WHERE CliCod = @CliCod
		IF(@@Error=0)
			RETURN 1
		RETURN -1
	END
END
GO


--devuelve todos los clientes de la tabla Clientes
CREATE PROCEDURE GetClientes AS
BEGIN 
	SELECT *
	FROM Clientes
END
GO

--Devueve todos los datos del cliente
CREATE PROCEDURE GetCliente 
			@CliCod int
		AS
BEGIN 
	SELECT *
	FROM Clientes
	WHERE CliCod = @CliCod
END
GO

-------------------------------- SISTEMAS --------------------------------------
--Agrega un sistema en Sistemas y retorna 1 si lo puede agregar, 
--0 si ya existe uno con el mismo nombre para el mismo cliente 
--y -1 si NO lo puede agregar, -2 si no existe el cliente 
--y -3 si ya existe un sistema con ese sitio 
CREATE	PROCEDURE AgregarSistema	
			@SisNom varchar(40),
			@CliCod int,
			@SisSit varchar(256),
			@SisUrlWS varchar(256)
		AS
BEGIN
	--Debe de existir el cliente al cual se le va a agregar el sistema
	IF (NOT EXISTS (SELECT * FROM Clientes WHERE CliCod = @CliCod ))
		RETURN -2 
	ELSE  
		--No se puede repetir el nombre de sistema para el cliente
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisNom=@SisNom AND CliCod=@CliCod))
			RETURN 0
	ELSE
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisSit=@SisSit))
			RETURN -3
		ELSE
		BEGIN
			DECLARE @Error int
			DECLARE @Id int

			BEGIN TRAN
			INSERT Sistemas( SisNom, CliCod, SisSit, SisUrlWS ) VALUES( @SisNom, @CliCod, @SisSit, @SisUrlWS );
			SET @Error=@@ERROR;	
			SET @Id= @@Identity

			IF(@Error=0)
			BEGIN
				COMMIT TRAN;
				RETURN @Id;
			END
			ELSE
			BEGIN
				ROLLBACK TRAN;
				RETURN -1;
			END	
		END
END
GO





--Elimina un Sistema. Retorna 1 si lo puede eliminar 0 si no esta, -1 si NO lo puede eliminar por alguna razon, 
--y -2 si el sistema tiene WorkPackages y en este caso no lo elimina
CREATE PROCEDURE EliminarSistema 
			@SisCod int 
		AS
BEGIN 
	
	IF (NOT EXISTS (SELECT * FROM Sistemas WHERE SisCod = @SisCod ))
		RETURN 0
	ELSE IF (EXISTS (SELECT * FROM WorkPackages WHERE SisCod = @SisCod))
		RETURN -2
	BEGIN 

		DECLARE @Error int
		BEGIN TRAN
		DELETE Sistemas
		WHERE  SisCod = @SisCod
		SET @Error=@@ERROR
		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN 1
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1
		END	
	END
END
GO


--Modifica el sistema de codigo @SisCod y retorna 1 si lo pudo hacer, 0 si no existe el cliente, -1 si NO lo pudo hacer, -2 si existe uno con ese nombre para el cliente
-- -3 si existe otro con el mismo sitio y -4 si no existe el cliente
CREATE PROCEDURE ModificarSistema
			@SisCod int,
			@SisNom varchar(40),
			@SisSit varchar(256),
			@SisUrlWS varchar(256),
			@CliCod int
		AS
BEGIN 
	IF (NOT EXISTS (SELECT * FROM Sistemas WHERE SisCod = @SisCod ))
		RETURN 0
	ELSE
	IF (NOT EXISTS (SELECT * FROM Clientes WHERE CliCod = @CliCod ))
		RETURN -4
	ELSE  
		--No se puede repetir el nombre de sistema para el cliente
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisNom=@SisNom AND CliCod=@CliCod AND SisCod <> @SisCod))
			RETURN -2
		ELSE
		IF(EXISTS (SELECT * FROM Sistemas WHERE SisSit=@SisSit AND SisCod <> @SisCod))
			RETURN -3
		ELSE
	BEGIN
		UPDATE Sistemas
		SET SisNom = @SisNom, SisSit = @SisSit, SisUrlWS = @SisUrlWS, CliCod = @CliCod
		WHERE SisCod = @SisCod
		IF(@@Error=0)
			RETURN 1
		RETURN -1
	END
END
GO


--devuelve todos los sistemas de pertenecientes a un cliente
CREATE PROCEDURE GetSistemas
			@CliCod int
		AS
BEGIN 
	SELECT *
	FROM Sistemas
	WHERE CliCod=@CliCod
END
GO

--devuelve los datos del Sistema con id indicado
CREATE PROCEDURE GetSistemasPorCod
			@SisCod int
		AS
BEGIN 
	SELECT *
	FROM Sistemas
	WHERE SisCod=@SisCod
END
GO



-------------------------------- WORK PACKAGES --------------------------------------
--Agrega un work package en WorkPackages y retorna el id del WP si lo puede agregar, 0 si ya existe uno con el mismo nombre para el mismo sistema y -1 si NO lo puede agregar
CREATE	PROCEDURE AgregarWorkPackage
			@WkpNom varchar(40),
			@WkpFchIni datetime,
			@WkpFchEnt datetime,
			@WkpFchEntEst datetime,
			@WkpFchFin datetime,
			@WkpEstCod int,
			@SisCod int
		AS
BEGIN 
	--No se puede repetir el nombre de de wp para el sistema
	IF (EXISTS (SELECT * FROM WorkPackages WHERE WkpNom = @WkpNom AND SisCod=@SisCod))
	BEGIN
		RETURN 0;
	END	
	ELSE
	BEGIN
		DECLARE @Error int
		BEGIN TRAN
		INSERT WorkPackages( WkpNom, WkpFchIni, WkpFchEnt, WkpFchEntEst, WkpFchFin, WkpEstCod, SisCod ) VALUES( @WkpNom, Convert(DateTime, @WkpFchIni, 103), Convert(DateTime, @WkpFchEnt, 103),Convert(DateTime, @WkpFchEntEst, 103),Convert(DateTime, @WkpFchFin, 103), @WkpEstCod, @SisCod );
		SET @Error=@@ERROR;		
		IF(@Error=0)
		BEGIN
			DECLARE @id int
			SET @id = (SELECT MAX(WkpCod) FROM WorkPackages)
			COMMIT TRAN;
			RETURN (@id);
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1;
		END	
	END
END
GO


--Modifica el WorkPackage de codigo @WkpCod y retorna 1 si lo pudo hacer, 0 si no existe el WorkPackage y -1 si NO lo pudo hacer
--y -2 si ya existe un workPackage con el mismo nombre para el mismo sistema
CREATE PROCEDURE ModificarWorkPackage
			@WkpCod int,
			@WkpNom varchar(40),
			@WkpFchIni datetime,
			@WkpFchEnt datetime,
			@WkpFchEntEst datetime,
			@WkpFchFin datetime,
			@WkpEstCod int
		AS
BEGIN 
	IF (NOT EXISTS (SELECT * FROM WorkPackages WHERE WkpCod = @WkpCod ))
		RETURN 0
	ELSE IF (EXISTS (SELECT * FROM WorkPackages WHERE 
					WkpCod != @WkpCod AND WkpNom = @WkpNom 
					AND  SisCod IN (SELECT SisCod FROM WorkPackages WHERE WkpCod = @WkpCod)))
		RETURN -2
	ELSE
	BEGIN
		UPDATE WorkPackages
		SET WkpNom = @WkpNom, 
			WkpFchIni = @WkpFchIni, 
			WkpFchEnt = @WkpFchEnt, 
			WkpFchEntEst = @WkpFchEntEst,
			WkpFchFin = @WkpFchFin,
			WkpEstCod = @WkpEstCod
		WHERE WkpCod = @WkpCod
		IF(@@Error=0)
			RETURN 1
		RETURN -1
	END
END
GO



--Devuelve todos los wp de un sistema 
CREATE PROCEDURE GetWorkPackagesSistema 
			@SisCod int
		AS
BEGIN 
	SELECT c.CliCod, c.CliNom, s.SisCod, s.SisNom, w.WkpCod, w.WkpNom, w.WkpFchIni, w.WkpFchEnt, w.WkpFchEntEst, w.WkpFchFin, e.WkpEstCod, e.WkpEstNom, e.WkpEstFin
	FROM ((WorkPackages w
			INNER JOIN 
			WorkPackagesEstados e 
			ON w.WkpEstCod = e.WkpEstCod AND w.SisCod = @SisCod ) 
			INNER JOIN 
			Sistemas s 
			ON w.SisCod = s.SisCod)
			INNER JOIN
			Clientes c
			ON c.CliCod = s.CliCod
END
GO

CREATE PROCEDURE GetWorkPackage
		@WkpCod int
	AS
BEGIN
	SELECT w.WkpCod, w.WkpNom, w.WkpFchIni, w.WkpFchEnt, w.WkpFchEntEst, w.WkpFchFin, w.SisCod, e.WkpEstCod, e.WkpEstNom, e.WkpEstFin
	FROM WorkPackages w INNER JOIN WorkPackagesEstados e 
	ON w.WkpEstCod = e.WkpEstCod AND w.WkpCod = @WkpCod			
END
GO



-------------------------------- WorkPackagesEstados --------------------------------------
--Devuelve el estado inicial de los WP
CREATE	PROCEDURE DarEstadoInicialWP	
		AS
BEGIN 
	SELECT *
	FROM WorkPackagesEstados
	WHERE WkpEstIni = 'S'
END
GO

--Devuelve el estado inicial de los WP
CREATE	PROCEDURE DarEstadoFinalWP	
		AS
BEGIN 
	SELECT *
	FROM WorkPackagesEstados
	WHERE WkpEstFin = 'S'
END
GO

CREATE	PROCEDURE GetEstadosWP	
		AS
BEGIN 
	SELECT *
	FROM WorkPackagesEstados
END
GO


-------------------------------- Usuarios --------------------------------------
--Agrega una persona en Usuarios y retorna 1 si la puede agregar, 0 si ya existe una con el mismo nombre y -1 si NO la puede agregar
CREATE	PROCEDURE AgregarUsuario	
			@UsrNom varchar(40)
		AS
BEGIN 

	--No se puede repetir el nombre de usuario
	IF (EXISTS (SELECT * FROM Usuarios WHERE UsrNom=@UsrNom))
	BEGIN
		RETURN 0;
	END	
	ELSE
	BEGIN
		DECLARE @Error int
		
		BEGIN TRAN
		INSERT Usuarios( UsrNom ) VALUES ( @UsrNom );
		SET @Error=@@ERROR;
		
		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN 1;
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1;
		END	
	END
END
GO


--Elimina un usuario retorna 1 si lo puede eliminar 0 si no esta y -1 si NO lo puede eliminar por alguna razon, 
CREATE PROCEDURE EliminarUsuario
			@UsrCod int 
		AS
BEGIN 
	
	IF (NOT EXISTS (SELECT * FROM Usuarios WHERE UsrCod = @UsrCod ))
		RETURN 0
	ELSE
	BEGIN 
		DECLARE @Error int
		BEGIN TRAN
		DELETE Usuarios
		WHERE  UsrCod = @UsrCod
		SET @Error=@@ERROR

		IF(@Error=0)
		BEGIN
			COMMIT TRAN;
			RETURN 1
		END
		ELSE
		BEGIN
			ROLLBACK TRAN;
			RETURN -1
		END	
	END
END
GO


--Modifica el usuario de codigo @UsrCod y retorna 1 si lo pudo hacer, 0 si no existe el cliente, -1 si NO lo pudo hacer
--y -2 si ya existe un usuario con ese nombre.
CREATE PROCEDURE ModificarUsuario
			@UsrCod int,
			@UsrNom varchar(40),
			@FechaSincronizacion datetime
		AS
BEGIN 
	IF (NOT EXISTS (SELECT * FROM Usuarios WHERE UsrCod = @UsrCod ))
		RETURN 0
	ELSE IF(EXISTS (SELECT * FROM Usuarios WHERE UsrCod != @UsrCod AND UsrNom = @UsrNom))
		RETURN -2
	ELSE
	BEGIN
		UPDATE Usuarios
		SET UsrNom = @UsrNom, FechaSincronizacion = @FechaSincronizacion
		WHERE UsrCod = @UsrCod
		IF(@@Error=0)
			RETURN 1
		RETURN -1
	END
END
GO

--devuelve todas las personas de la tabla Usuarios
CREATE PROCEDURE GetUsuarios AS
BEGIN 
	SELECT *
	FROM Usuarios
END
GO

--devuelve la persona con nombre UsrNom
CREATE PROCEDURE GetUsuarioPorNombre 
@UsrNom varchar(40) 
AS
BEGIN 
	SELECT *
	FROM Usuarios
	WHERE UsrNom = @UsrNom
END
GO

--devuelve la persona con codigo UsrCod
CREATE PROCEDURE GetUsuario 
@UsrCod int 
AS
BEGIN 
	SELECT *
	FROM Usuarios
	WHERE UsrCod = @UsrCod
END
GO